# @FileName  :  course.py
# @Time      :  2025/7/29
# @Author    :  Ayanami
# @info      :  课程CRUD操作

from typing import Optional, List, Tuple
from datetime import datetime
from tortoise.exceptions import DoesNotExist, IntegrityError
from app.models.models import Course, Teacher, Agent
from app.schemas.course import CourseCreate, CourseUpdate, CourseQuery


class CourseCRUD:
    """课程CRUD操作类"""

    @staticmethod
    async def create(course_data: CourseCreate) -> Course:
        """创建课程"""
        try:
            # 验证外键是否存在
            teacher = await Teacher.get_or_none(id=course_data.teacher_id, is_deleted=False)
            if not teacher:
                raise ValueError("指定的讲师不存在")

            agent = await Agent.get_or_none(id=course_data.agent_id, is_deleted=False)
            if not agent:
                raise ValueError("指定的培训机构不存在")

            course_dict = course_data.model_dump()
            course = await Course.create(**course_dict)
            # 重新获取包含关联对象的课程
            return await Course.get(id=course.id).select_related('teacher', 'agent', 'category')
        except IntegrityError as e:
            raise ValueError(f"数据完整性错误,{str(e)}")

    @staticmethod
    async def get_by_id(course_id: int) -> Optional[Course]:
        """根据ID获取课程"""
        try:
            return await Course.get(id=course_id, is_deleted=False).select_related('teacher', 'agent', 'category')
        except DoesNotExist:
            return None

    @staticmethod
    async def update(course_id: int, course_data: CourseUpdate) -> Optional[Course]:
        """更新课程信息"""
        try:
            course = await Course.get(id=course_id, is_deleted=False)
            update_data = course_data.model_dump(exclude_unset=True)

            if update_data:
                # 验证外键
                if 'teacher_id' in update_data:
                    teacher = await Teacher.get_or_none(id=update_data['teacher_id'])
                    if not teacher:
                        raise ValueError("指定的讲师不存在")

                if 'agent_id' in update_data:
                    agent = await Agent.get_or_none(id=update_data['agent_id'])
                    if not agent:
                        raise ValueError("指定的培训机构不存在")

                await course.update_from_dict(update_data)
                await course.save()

            return await Course.get(id=course_id).select_related('teacher', 'agent', 'category')
        except DoesNotExist:
            return None
        except IntegrityError as e:
            raise ValueError(f"数据完整性错误,{e}")

    @staticmethod
    async def delete(course_id: int) -> bool:
        """删除课程"""
        try:
            course = await Course.get(id=course_id, is_deleted=False)
            course.is_deleted = True
            await course.save(update_fields=['is_deleted'])
            return True
        except DoesNotExist:
            return False

    @staticmethod
    async def get_list(query: CourseQuery) -> Tuple[List[Course], int]:
        """获取课程列表"""
        queryset = Course.filter(is_deleted=False).select_related('teacher', 'agent', 'category')

        # 构建查询条件
        if query.title:
            queryset = queryset.filter(title__icontains=query.title)
        if query.category_id is not None:
            queryset = queryset.filter(category=query.category)
        if query.is_online is not None:
            queryset = queryset.filter(is_online=query.is_online)
        if query.is_premium is not None:
            queryset = queryset.filter(is_premium=query.is_premium)
        if query.enable_checkin is not None:
            queryset = queryset.filter(enable_checkin=query.enable_checkin)
        if query.teacher_id:
            queryset = queryset.filter(teacher_id=query.teacher_id)
        if query.agent_id:
            queryset = queryset.filter(agent_id=query.agent_id)
        if query.parent_course is not None:
            queryset = queryset.filter(parent_course=query.parent_course)
        if query.schedule_start:
            queryset = queryset.filter(schedule__gte=query.schedule_start)
        if query.schedule_end:
            queryset = queryset.filter(schedule__lte=query.schedule_end)
        if query.tags:
            # JSON字段标签查询
            for tag in query.tags:
                queryset = queryset.filter(tags__contains=[tag])

        # 获取总数
        total = await queryset.count()

        # 分页
        courses = await queryset.offset(query.skip).limit(query.limit).order_by('-created_at')

        return courses, total

    @staticmethod
    async def get_by_teacher(teacher_id: int) -> List[Course]:
        """获取指定教师的课程"""
        return await Course.filter(teacher_id=teacher_id, is_deleted=False).select_related('teacher', 'agent', 'category').order_by('-created_at')

    @staticmethod
    async def get_by_agent(agent_id: int) -> List[Course]:
        """获取指定机构的课程"""
        return await Course.filter(agent_id=agent_id, is_deleted=False).select_related('teacher', 'agent', 'category').order_by('-created_at')

    @staticmethod
    async def get_sub_courses(parent_course_id: int) -> List[Course]:
        """获取子课程"""
        return await Course.filter(parent_course=parent_course_id, is_deleted=False).select_related('teacher', 'agent', 'category').order_by('created_at')

    @staticmethod
    async def get_premium_courses(limit: int = 10) -> List[Course]:
        """获取精品课程"""
        return await Course.filter(is_premium=True, is_deleted=False).select_related('teacher', 'agent', 'category').limit(limit).order_by('-created_at')

    @staticmethod
    async def get_upcoming_courses(limit: int = 10) -> List[Course]:
        """获取即将开始的课程"""
        now = datetime.now()
        return await Course.filter(
            schedule__gt=now,
            is_deleted=False
        ).select_related('teacher', 'agent', 'category').limit(limit).order_by('schedule')

    @staticmethod
    async def search_courses(keyword: str, limit: int = 10) -> List[Course]:
        """搜索课程"""
        return await Course.filter(
            title__icontains=keyword,
            is_deleted=False
        ).select_related('teacher', 'agent', 'category').limit(limit)

    @staticmethod
    async def increment_enrollment_count(course_id: int) -> bool:
        """增加报名人数"""
        try:
            course = await Course.get(id=course_id, is_deleted=False)
            course.enrollment_count += 1
            await course.save()
            return True
        except DoesNotExist:
            return False

    @staticmethod
    async def decrement_enrollment_count(course_id: int) -> bool:
        """减少报名人数"""
        try:
            course = await Course.get(id=course_id, is_deleted=False)
            if course.enrollment_count > 0:
                course.enrollment_count -= 1
                await course.save()
            return True
        except DoesNotExist:
            return False

    @staticmethod
    async def get_statistics() -> dict:
        """获取课程统计信息"""
        total = await Course.filter(is_deleted=False).count()
        online = await Course.filter(is_online=True, is_deleted=False).count()
        offline = await Course.filter(is_online=False, is_deleted=False).count()
        premium = await Course.filter(is_premium=True, is_deleted=False).count()

        # 获取所有课程的报名人数进行计算
        courses = await Course.filter(is_deleted=False).values('enrollment_count')
        enrollment_counts = [course['enrollment_count'] for course in courses]

        total_enrollments = sum(enrollment_counts)
        avg_enrollment = sum(enrollment_counts) / len(enrollment_counts) if enrollment_counts else 0

        return {
            'total_courses': total,
            'online_courses': online,
            'offline_courses': offline,
            'premium_courses': premium,
            'total_enrollments': total_enrollments,
            'avg_enrollment': round(avg_enrollment, 2)
        }

    @staticmethod
    async def get_courses_by_category(category: int) -> List[Course]:
        """根据分类获取课程"""
        return await Course.filter(category=category, is_deleted=False).select_related('teacher', 'agent', 'category').order_by('-created_at')

    @staticmethod
    async def get_courses_by_tags(tags: List[str]) -> List[Course]:
        """根据标签获取课程"""
        queryset = Course.all()
        for tag in tags:
            queryset = queryset.filter(tags__contains=[tag], is_deleted=False)
        return await queryset.select_related('teacher', 'agent', 'category').order_by('-created_at')


# 创建CRUD实例
course_crud = CourseCRUD()